package ro.gateway.aida.obj.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;

import ro.gateway.aida.db.DBPersistenceManager;
import ro.gateway.aida.db.PersistenceToken;
import ro.gateway.aida.obj.AIDAActivityObject;
import ro.gateway.aida.obj.AIDALocation;
import ro.gateway.aida.obj.CRegion;
import ro.gateway.aida.obj.Country;
import ro.gateway.aida.obj.County;
import ro.gateway.aida.obj.Region;
import ro.gateway.aida.obj.Town;
import ro.gateway.aida.servlet.Constants;


/**
 * <p>Title: Romanian AIDA</p> <p>Description: :D application</p> <p>Copyright: Copyright (comparator) 2003</p>
 * <p>Company: Romania Development Gateway </p>
 *
 * @author Mihai Popoaei, mihai_popoaei@yahoo.com, smike@intellisource.ro
 * @version 1.0-* @version $Id: AIDALocationDB.java,v 1.2 2005/01/26 10:35:58 mihaipostelnicu Exp $
 */
public class AIDALocationDB extends DBPersistenceManager {

	private AIDALocationDB(PersistenceToken token) {
		super(token);
	}

	public static AIDALocationDB getManager(PersistenceToken token) {
		return new AIDALocationDB(token);
	}

	public Country[] getCountriesByCRegion(int cregion_id) throws SQLException {

		Country[] result = null;
		ArrayList items = new ArrayList();

		Connection con = getConnection();

		try {
			PreparedStatement ps = con.prepareStatement(GET_ALL_BY_CREGION);
			ps.setInt(1, cregion_id);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				Country item = new Country();

				item.setName(rs.getString(1));
				item.setIso3(rs.getString(2));

				items.add(item);
			}
			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}

		if (items.size() > 0) {
			result = new Country[items.size()];
			items.toArray(result);
		}

		return result;
	}

	public Country[] getAllCountries() throws SQLException {

		Country[] result = null;
		ArrayList items = new ArrayList();

		Connection con = getConnection();

		try {
			PreparedStatement ps = con.prepareStatement(GET_ALL);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				Country item = new Country();

				item.setName(rs.getString(1));
				item.setIso3(rs.getString(2));

				items.add(item);
			}
			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}

		if (items.size() > 0) {
			result = new Country[items.size()];
			items.toArray(result);
		}

		return result;
	}

	public Country getCountryByISO3(String iso3) throws SQLException {
		if (iso3 == null) return null;

		Connection con = getConnection();
		Country result = null;

		try {
			PreparedStatement ps = con.prepareStatement(GET_BY_ISO3);
			ps.setString(1, iso3);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				result = new Country();
				result.setIso3(iso3);
				result.setName(rs.getString(1));
				result.setCregion_id(rs.getInt(2));
				result.getGeo_data().setLatitude(rs.getDouble(3));
				result.getGeo_data().setLongitude(rs.getDouble(4));
				result.getGeo_data().setGeo_code(rs.getString(5));
			}
			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}

		return result;
	}

	public AIDALocation getCountryLocationByISO3(String iso3)
		 throws SQLException {

		Connection con = getConnection();
		AIDALocation result = null;

		try {
			PreparedStatement ps = con.prepareStatement(GET_BY_ISO3);
			ps.setString(1, iso3);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				result = new AIDALocation();
				Country country = new Country();
				country.setIso3(iso3);
				country.setName(rs.getString(1));
				result.setCountry(country);
			}
			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}

		return result;
	}

	public void insertLocation(AIDALocation item)
		 throws SQLException {

		AIDAActivityObjectDB.getManager(token)
			 .insert(db_token.getIdgenerators(), item);
		Connection con = getConnection();

		try {
			PreparedStatement ps = con.prepareStatement(INSERT);
			ps.setLong(1, item.getId());
			if (item.getCountry() == null) {
				ps.setNull(2, Types.VARCHAR);
				ps.setNull(3, Types.VARCHAR);
			} else {
				if (item.getCountry().getIso2() != null) {
					ps.setString(2, item.getCountry().getIso2());
				} else {
					ps.setNull(2, Types.VARCHAR);
				}
				ps.setString(3, item.getCountry().getIso3());
			}
			if (item.getReg_id() == -1) {
				ps.setNull(4, Types.INTEGER);
			} else {
				ps.setInt(4, item.getReg_id());
			}
			if (item.getJud_id() == -1) {
				ps.setNull(5, Types.INTEGER);
			} else {
				ps.setInt(5, item.getJud_id());
			}
			if (item.getSirsup_id() == -1) {
				ps.setNull(6, Types.INTEGER);
			} else {
				ps.setInt(6, item.getSirsup_id());
			}
			if (item.getSiruta_id() == -1) {
				ps.setNull(7, Types.INTEGER);
			} else {
				ps.setInt(7, item.getSiruta_id());
			}
			if (item.getCregion_id() == -1) {
				ps.setNull(8, Types.INTEGER);
			} else {
				ps.setInt(8, item.getCregion_id());
			}
			ps.executeUpdate();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}

		return;
	}

	public AIDALocation[] getForActivity(long activity_id)
		 throws SQLException {

		Connection con = getConnection();
		AIDALocation[] result = null;
		ArrayList items = new ArrayList();

		try {
			PreparedStatement ps = con.prepareStatement(GET_FOR_ACTIVITY);
			ps.setLong(1, activity_id);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				AIDALocation item = new AIDALocation();

				item.setId(rs.getLong(1));
				item.setCountry(getCountryByISO3(rs.getString(2)));
				int reg_id = rs.getInt(3);
				if (rs.wasNull() || (reg_id == -1)) {
					item.setRegion(null);
				} else {
					item.setRegion(RegionDB.getManager(token).getById(reg_id));
				}
				int jud_id = rs.getInt(4);
				if (rs.wasNull() || (jud_id == -1)) {
					item.setJudet(null);
				} else {
					item.setJudet(CountyDB.getManager(token).getById(jud_id));
				}
				int oras_id = rs.getInt(5);
				if (rs.wasNull() || (oras_id == -1)) {
					item.setOras(null);
				} else {
					item.setOras(TownDB.getManager(token).getById(oras_id));
				}
				int loc_id = rs.getInt(6);
				if (rs.wasNull() || (loc_id == -1)) {
					item.setLocalitate(null);
				} else {
					item.setLocalitate(VillageDB.getManager(token).getById(loc_id));
				}
				int cregion_id = rs.getInt(7);
				if (rs.wasNull() || cregion_id == -1) {
					item.setCregion(null);
				} else {
					item.setCregion(AIDALocationDB.getManager(db_token).getCRegionById(cregion_id));
				}

				items.add(item);
			}
			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}

		if (items.size() > 0) {
			result = new AIDALocation[items.size()];
			items.toArray(result);
		}

		return result;
	}

	public final void delete(long id)
		 throws SQLException {

		Connection con = getConnection();

		try {
			PreparedStatement ps = con.prepareStatement(DELETE);
			ps.setLong(1, id);
			ps.executeUpdate();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}

		return;
	}

	public void delete_for_activity(long id) throws SQLException {

		Connection con = getConnection();

		String database_pname = con.getMetaData().getDatabaseProductName();
		if (0 == database_pname.compareToIgnoreCase("MYSQL")) {
			System.out.println("MYSQL - deleteForActivity (documents) -- DEBUG");
			con.close();
			deleteForActivityMYSQL(id);
			return;
		}

		try {
			PreparedStatement ps = con.prepareStatement(DELETE_for_activity);
			ps.setLong(1, id);
			ps.executeUpdate();
			ps.close();

			AIDAActivityObjectDB.getManager(token).deleteForActivityByType(con,
				 id,
				 AIDAActivityObject.TYPE_LOCATION);
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}

		return;
	}

	private void deleteForActivityMYSQL(long id) throws SQLException {

		AIDALocation[] locations = getForActivity(id);

		if (locations != null) {
			Connection con = getConnection();
			try {
				PreparedStatement ps =
					 con.prepareStatement("DELETE FROM locations WHERE id = ?");
				for (int i = 0; i < locations.length; i++) {
					ps.clearParameters();
					ps.setLong(1, locations[i].getId());
					ps.executeUpdate();
				}
				ps.close();

				AIDAActivityObjectDB.getManager(token).deleteForActivityByType(con,
					 id,
					 AIDAActivityObject.TYPE_LOCATION);
			} catch (SQLException sqlEx) {
				throw sqlEx;
			} finally {
				con.close();
			}
		}
	}

	public AIDALocation[] findLocations(String what, int max_result_len)
		 throws SQLException {
		AIDALocation[] result = null;

		AIDALocation countries[] = findCountries(what, max_result_len);
		if (countries == null)
			countries = new AIDALocation[0];

		AIDALocation[] regions =
			 RegionDB.getManager(token).findRegions(what, max_result_len);
		if (regions == null)
			regions = new AIDALocation[0];

		AIDALocation[] counties =
			 CountyDB.getManager(token).findCounties(what, max_result_len);
		if (counties == null)
			counties = new AIDALocation[0];

		AIDALocation[] towns =
			 TownDB.getManager(token).findTowns(what, max_result_len);
		if (towns == null)
			towns = new AIDALocation[0];

		AIDALocation[] villages =
			 VillageDB.getManager(token).findVillages(what, max_result_len);
		if (villages == null)
			villages = new AIDALocation[0];

		result =
			 new AIDALocation[countries.length
			 + regions.length
			 + counties.length
			 + towns.length
			 + villages.length];
		System.arraycopy(countries, 0, result, 0, countries.length);
		System.arraycopy(regions, 0, result, countries.length, regions.length);
		System.arraycopy(counties,
			 0,
			 result,
			 countries.length + regions.length,
			 counties.length);
		System.arraycopy(towns,
			 0,
			 result,
			 countries.length + regions.length + counties.length,
			 towns.length);
		System.arraycopy(villages,
			 0,
			 result,
			 countries.length + regions.length + counties.length + towns.length,
			 villages.length);

		return result;
	}

	public AIDALocation[] findCountries(String what, int max_result_len)
		 throws SQLException {
		if (what == null)
			return null;

		ArrayList items = new ArrayList();
		AIDALocation[] result = null;
		Connection con = getConnection();

		try {
			PreparedStatement ps = con.prepareStatement(FIND_COUNTRIES);
			ps.setString(1, what.toUpperCase() + "%");
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				AIDALocation item = new AIDALocation();
				Country country = new Country();
				country.setName(rs.getString(1));
				country.setIso3(rs.getString(2));
				country.setCregion_id(rs.getInt(3));
				item.setCountry(country);
				items.add(item);

				if (items.size() > max_result_len) {
					break;
				}
			}
			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}

		if (items.size() > 0) {
			result = new AIDALocation[items.size()];
			items.toArray(result);
		}
		return result;
	}

	public AIDALocation[] findCountriesIdentical(String what, int max_result_len)
		 throws SQLException {
		if (what == null)
			return null;

		ArrayList items = new ArrayList();
		AIDALocation[] result = null;
		Connection con = getConnection();

		try {
			PreparedStatement ps = con.prepareStatement(FIND_COUNTRIES);
			ps.setString(1, what.toUpperCase());
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				AIDALocation item = new AIDALocation();
				Country country = new Country();
				country.setName(rs.getString(1));
				country.setIso3(rs.getString(2));
				item.setCountry(country);
				items.add(item);

				if (items.size() > max_result_len) {
					break;
				}
			}
			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}

		if (items.size() > 0) {
			result = new AIDALocation[items.size()];
			items.toArray(result);
		}
		return result;
	}

	public CRegion getCRegionById(int id)
		 throws SQLException {
		Connection con = getConnection();
		CRegion result = null;
		try {
			PreparedStatement ps = con.prepareStatement(GET_CREGION);
			ps.setInt(1, id);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				result = new CRegion();
				result.setId(id);
				result.setName(rs.getString(1));
				result.getGeo_data().setLatitude(rs.getDouble(2));
				result.getGeo_data().setLongitude(rs.getDouble(3));
				result.getGeo_data().setGeo_code(rs.getString(4));
				result.setCode(rs.getString(5));
				result.setParent_id(rs.getInt(6));
			}
			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}
		return result;
	}

	public CRegion[] getAllCRegions()
		 throws SQLException {
		Connection con = db_token.getCFService().
			 getConnection(db_token.getPool_name());
		CRegion[] result = null;
		ArrayList items = new ArrayList();
		try {
			PreparedStatement ps = con.prepareStatement(GET_ALL_CREGIONS);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				CRegion item = new CRegion();
				item.setId(rs.getInt(1));
				item.setName(rs.getString(2));
				items.add(item);
			}
			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}
		if (items.size() > 0) {
			result = new CRegion[items.size()];
			items.toArray(result);
		}
		return result;
	}

	public CRegion[] findCRegion(String what)
		 throws SQLException {
		Connection con = db_token.getCFService().
			 getConnection(db_token.getPool_name());
		CRegion[] result = null;
		ArrayList items = new ArrayList();
		try {
			PreparedStatement ps = con.prepareStatement(FIND_CREGIONS);
			ps.setString(1, what.toUpperCase() + "%");
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				CRegion item = new CRegion();
				item.setId(rs.getInt(1));
				item.setName(rs.getString(2));
				item.setCode(rs.getString(3));
				items.add(item);
			}
			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}
		if (items.size() > 0) {
			result = new CRegion[items.size()];
			items.toArray(result);
		}
		return result;
	}

	public void update_region_for_country(String country_iso3,
								   int region_id)
		 throws SQLException {
		Connection con = db_token.getCFService().
			 getConnection(db_token.getPool_name());

		try {
			PreparedStatement ps = con.prepareStatement(UPDATE_CNTR_REG_LINK);
			ps.setInt(1, region_id);
			ps.setString(2, country_iso3);
			ps.executeUpdate();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}
	}

	public void updateCregion(CRegion region)
		 throws SQLException {
		if (region == null) return;

		Connection con = db_token.getCFService().
			 getConnection(db_token.getPool_name());
		if (region.getId() == -1) {
			region.setId((int) db_token.getIdgenerators().
				 nextVal(Constants.IDGEN_CREGIONS));
		}

		try {
			PreparedStatement ps = con.prepareStatement(UPDATE_CREGION);
			ps.setString(1, region.getName());
			ps.setDouble(2, region.getGeo_data().getLatitude());
			ps.setDouble(3, region.getGeo_data().getLongitude());
			ps.setString(4, region.getGeo_data().getGeo_code());
			ps.setString(5, region.getCode());
			ps.setInt(6, region.getParent_id());
			ps.setInt(7, region.getId());
			ps.executeUpdate();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}
	}

	public void insertCRegion(CRegion region)
		 throws SQLException {
		if (region == null) return;

		Connection con = db_token.getCFService().
			 getConnection(db_token.getPool_name());
		if (region.getId() == -1) {
			region.setId((int) db_token.getIdgenerators().
				 nextVal(Constants.IDGEN_CREGIONS));
		}

		try {
			PreparedStatement ps = con.prepareStatement(INSERT_CREGION);
			ps.setInt(1, region.getId());
			ps.setString(2, region.getName());
			ps.setDouble(3, region.getGeo_data().getLatitude());
			ps.setDouble(4, region.getGeo_data().getLongitude());
			ps.setString(5, region.getGeo_data().getGeo_code());
			ps.setString(6, region.getCode());
			ps.setInt(7, region.getParent_id());
			ps.executeUpdate();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}
	}

	public int getLastCRegionId()
		 throws SQLException {
		int result = 0;

		Connection con = db_token.getCFService().
			 getConnection(db_token.getPool_name());
		try {
			PreparedStatement ps = con.prepareStatement(GET_MAX_CREGION_ID);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				result = rs.getInt(1);
			}
			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}

		return result;
	}

	public AIDALocation getOrCreateLocation(String s_cregion,
									String s_country,
									String s_county,
									String s_town)
		 throws SQLException {
		if (s_cregion != null) s_cregion = s_cregion.toUpperCase();
		if (s_country != null) s_country = s_country.toUpperCase();
		if (s_county != null) s_county = s_county.toUpperCase();
		if (s_town != null) s_town = s_town.toUpperCase();

		AIDALocation result = null;
		Town town = null;
		County county = null;

		while (true) {
			if (s_town != null) {
				AIDALocation[] towns = TownDB.getManager(db_token)
					 .findTownsIdentical(s_town, 10);
				if (towns != null) {
					result = towns[0];
					break;
				}
				town = new Town();
				town.setName(s_town);
			}

			if (s_county != null) {
				AIDALocation[] counties = CountyDB.getManager(db_token)
					 .findCountiesIdentical(s_county, 10);
				if (counties != null) {
					if (result != null) {
						result.setJudet(counties[0].getJudet());
					} else {
						result = counties[0];
						break;
					}
				}
				county = new County();
				county.setName(s_county);
			}

			if (s_country != null) {
				AIDALocation[] countries = AIDALocationDB.getManager(db_token)
					 .findCountriesIdentical(s_country, 10);
				if (countries != null) {
					if (result != null) {
						result.setCountry(countries[0].getCountry());
					} else {
						result = countries[0];
						break;
					}
				}
			}

			break;
		}

		if (result == null) {
			System.out.println("can't create (no country):" +
				 s_cregion + "," + s_country + "," + s_county + "," + s_town);
			return null;
		}

		if (county != null && result.getJudet() == null) {
			Region region = new Region();
			region.setName(county.getName());
			region.setCountryIso3(result.getCountry().getIso3());
			RegionDB.getManager(db_token).insertRegion(region);

			county.setRegiune_id(region.getId());
			CountyDB.getManager(db_token).insertCounty(county);
			result.setJudet(county);
		}

		if (town != null && result.getOras() == null) {
			if (result.getJudet() == null) {
				Region region = new Region();
				region.setName(town.getName());
				region.setCountryIso3(result.getCountry().getIso3());
				RegionDB.getManager(db_token).insertRegion(region);

				county = new County();
				county.setName(town.getName());
				county.setRegiune_id(region.getId());
				CountyDB.getManager(db_token).insertCounty(county);
				result.setJudet(county);
			}

			town.setJudet_id(result.getJudet().getId());
			TownDB.getManager(db_token).insertTown(town);
			result.setOras(town);
		}

		return result;
	}

	public void deleteCRegion(int id)
		 throws Exception {
		Connection con = db_token.getCFService().
			 getConnection(db_token.getPool_name());

		try {
			PreparedStatement ps = con.prepareStatement(DELETE_CREGION);
			ps.setInt(1, id);
			ps.executeUpdate();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}
	}

	public void deleteCountry(String iso3)
		 throws Exception {
		Connection con = db_token.getCFService().
			 getConnection(db_token.getPool_name());

		try {
			PreparedStatement ps = con.prepareStatement(DELETE_COUNTRY);
			ps.setString(1, iso3);
			ps.executeUpdate();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}
	}

	public void insertCountry(Country country)
		 throws Exception {
		Connection con = db_token.getCFService().
			 getConnection(db_token.getPool_name());

		try {
			PreparedStatement ps = con.prepareStatement(INSERT_COUNTRY);
			ps.setString(1, country.getIso3());
			ps.setString(2, country.getName());
			ps.setInt(3, country.getCregion_id());
			ps.setDouble(4, country.getGeo_data().getLatitude());
			ps.setDouble(5, country.getGeo_data().getLongitude());
			ps.setString(6, country.getGeo_data().getGeo_code());
			ps.executeUpdate();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}
	}

	public CRegion[] getRootCregions(boolean get_children) throws SQLException {
		Connection con = db_token.getCFService().
			 getConnection(db_token.getPool_name());

		CRegion[] result = null;
		ArrayList items = new ArrayList();
		try {
			PreparedStatement ps = con.prepareStatement(GET_ROOT_CREGIONS);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				CRegion item = new CRegion();
				item.setId(rs.getInt(1));
				item.setName(rs.getString(2));
				items.add(item);

				if (get_children) {
					item.setChildren(getCregionsByParent(item.getId()));
				}
			}
			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}

		if (items.size() > 0) {
			result = new CRegion[items.size()];
			items.toArray(result);
		}
		return result;
	}

	public CRegion[] getCregionsByParent(int parent_id) throws SQLException {
		Connection con = db_token.getCFService().
			 getConnection(db_token.getPool_name());

		CRegion[] result = null;
		ArrayList items = new ArrayList();
		try {
			PreparedStatement ps = con.prepareStatement(GET_CREGIONS_BY_PARENT);
			ps.setLong(1, parent_id);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				CRegion item = new CRegion();
				item.setId(rs.getInt(1));
				item.setName(rs.getString(2));
				item.setParent_id(parent_id);
				items.add(item);
			}
			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}

		if (items.size() > 0) {
			result = new CRegion[items.size()];
			items.toArray(result);
		}
		return result;
	}

	public void updateCountry(Country country)
		 throws Exception {
		Connection con = getConnection();

		try {
			PreparedStatement ps = con.prepareStatement(UPDATE_COUNTRY);
			ps.setString(1, country.getName());
			ps.setString(2, country.getGeo_data().getGeo_code());
			ps.setDouble(3, country.getGeo_data().getLatitude());
			ps.setDouble(4, country.getGeo_data().getLongitude());
			ps.setString(5, country.getIso3());
			ps.executeUpdate();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}
	}

	private static final String GET_ALL =
		 "SELECT name, iso3 FROM countries_iso3 ORDER BY name ASC";
	private static final String GET_ALL_BY_CREGION =
		 "SELECT name, iso3 FROM countries_iso3 WHERE cregion_id=? ORDER BY name ASC";

	private static final String GET_BY_ISO3 =
		 "SELECT name,cregion_id,gs_lat,gs_long,geo_code FROM countries_iso3 WHERE iso3 = ?";

	private static final String INSERT =
		 "INSERT INTO locations (id, country_iso2, country_iso3, reg_id, "
		 + "jud_id, sirsup_id, siruta_id, cregion_id) "
		 + "VALUES (?,?,?,?,?,?,?,?)";

	private static final String GET_FOR_ACTIVITY =
		 "SELECT l.id, l.country_iso3, l.reg_id, "
		 + "l.jud_id, l.sirsup_id, l.siruta_id, l.cregion_id "
		 + "FROM locations l, objects o "
		 + "WHERE l.id=o.id AND o.activity_id=?";

	private static final String GET_ALL_CREGIONS =
		 "SELECT cr.id, cr.name " +
		 "FROM cregions cr " +
		 "ORDER BY cr.name";
	private static final String GET_ROOT_CREGIONS =
		 "SELECT cr.id, cr.name " +
		 "FROM cregions cr WHERE parent IS NULL " +
		 "ORDER BY cr.name";
	private static final String GET_CREGIONS_BY_PARENT =
		 "SELECT cr.id, cr.name " +
		 "FROM cregions cr WHERE parent=? " +
		 "ORDER BY cr.name";
	private static final String FIND_CREGIONS =
		 "SELECT cr.id, cr.name, cr.code " +
		 "FROM cregions cr " +
		 "WHERE name ILIKE ?" +
		 "ORDER BY cr.name";


	private static final String GET_CREGION =
		 "SELECT name,gs_lat,gs_long,geo_code,cr_code,parent FROM cregions WHERE id=?";

	private static final String INSERT_CREGION =
		 "INSERT INTO cregions (id,name,gs_lat,gs_long,geo_code,cr_code,parent) VALUES (?,?,?,?,?,?,?)";
	private static final String UPDATE_CREGION =
		 "UPDATE cregions SET name=?,gs_lat=?,gs_long=?,geo_code=?,cr_code=?,parent=? WHERE id=?";
	private static final String DELETE_CREGION =
		 "DELETE FROM cregions WHERE id = ?";
	private static final String DELETE_COUNTRY =
		 "DELETE FROM countries_iso3 WHERE iso3=?";

	private static final String DELETE = "DELETE FROM locations WHERE id = ?";
	private static final String DELETE_for_activity =
		 "DELETE FROM locations WHERE id IN (SELECT id FROM objects WHERE activity_id=?)";

	private static final String FIND_COUNTRIES =
		 "SELECT name, iso3, cregion_id FROM countries_iso3 WHERE name ILIKE ?";
	private static final String INSERT_COUNTRY =
		 "INSERT INTO countries_iso3 (iso3,name,cregion_id,gs_lat,gs_long,geo_code) VALUES (?,?,?,?,?,?)";
	private static final String GET_MAX_CREGION_ID =
		 "SELECT max(id) FROM cregions";
	private static final String UPDATE_CNTR_REG_LINK =
		 "UPDATE countries_iso3 SET cregion_id=? WHERE iso3=?";
	private static final String UPDATE_COUNTRY =
		 "UPDATE countries_iso3 SET name=?, geo_code=?, gs_lat=?, gs_long=? WHERE  iso3=?";


}